package com.report.reportserver.controller;

import com.report.reportserver.service.SysSqlService;
import com.report.reportserver.utils.ExcelUtil;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.multipart.MultipartFile;

import javax.servlet.http.HttpServletRequest;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * @ ClassName: DatabaseController
 * @ Description：直接对数据库进行操作
 * @ Modified By：
 * @ Version:
 * @ Author     ：LiYing
 * @ Date       ：Created in 14:29 2022/4/7
 */
@Controller
@RequestMapping(value = "/database")

public class DatabaseController {
    @Autowired
    SysSqlService sysSqlService;

    /*
     * 删除数据库表
     * @param request
     * @return boolean
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/7 22:48
     */
    @RequestMapping(value = "/delete")
    public @ResponseBody
    boolean delete(String tableName) {
        return sysSqlService.dropDatabaseTable(tableName);
    }

    /*
     * 创建数据库表
     * @param request
     * @return boolean
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/7 22:50
     */
    @RequestMapping(value = "/create")
    public @ResponseBody
    boolean create(String sql) {
        int ans = sysSqlService.createDatabaseTable(sql);
        return true;
    }

    /*
     * 查询数据库中某个表的基本信息
     * @param request
     * @return java.util.Map
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/7 22:53
     */
    @RequestMapping(value = "/tableinfo")
    public @ResponseBody
    Map getTableInfo(String tableName) {
        //String tableName = request.getParameter("tableName");
        Map map = sysSqlService.getTableInfoMap(tableName);
        return map;
    }

    /*
     * 根据key确定某行并且将其值进行更新
     * @param request
     * @return java.lang.String
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/8 9:37
     */
    @RequestMapping(value = "/updatecolumn")
    public @ResponseBody
    String updateColumnValueByKey(String tableName,String columnName,String key,Integer keyId,Object value) {
//        String tableName = request.getParameter("tableName");
//        String columnName = request.getParameter("columnName");
//        String key = request.getParameter("key");
//        Integer keyId = Integer.parseInt(request.getParameter("keyId"));
//        Object value = request.getParameter("value");
        sysSqlService.updateColumnValueByKey(tableName, columnName, key, keyId, value);
        return null;
    }

    /*
     * 解析没有明确规定的sql查询并返回对象
     * @param null
     * @return
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/10 17:45
     */
    @RequestMapping(value = "/sql")
    public @ResponseBody
    List getIdNameSelectList(String sql) {
        //String sql = request.getParameter("sql");
        return sysSqlService.getSelectList(sql);
    }

    @RequestMapping(value = "/createbyexcel")
    public @ResponseBody
    String createTableByExcel(@RequestParam("file") MultipartFile file) {
        String name = file.getOriginalFilename();
        String tableName = name.split("\\.")[0];

        if (!name.substring(name.length() - 4).equals(".xls") && !name.substring(name.length() - 5).equals(".xlsx")) {
            return "文件解析错误";
        }
        //存储列名
        List<String> cols = new ArrayList<>();
        try {
            cols = ExcelUtil.excelToTable(file.getInputStream());
            //创建表
            sysSqlService.createDatabaseTableByColName(cols, tableName);

            //插入数据
            List<List<String>> objects;
            objects = ExcelUtil.excelToData(file.getInputStream());
            sysSqlService.insertData(objects, cols, tableName);
            return "done";
        } catch (Exception e) {
            e.printStackTrace();
            return "error";
        }
    }

    @RequestMapping(value = "/tablenames")
    public @ResponseBody
    List getTableNames() {
        return sysSqlService.getTableNames();
    }

    @RequestMapping(value = "/insertsql")
    /*
     * 通过一条自定义sql语句直接插入一条数据
     * @param sql
     * @return void
     * @author LiYing
     * @creed: Talk is cheap,show me the code
     * @date 2022/4/21 15:15
     */
    public @ResponseBody String insertDataBySql(String sql){
        if(sysSqlService.insertDataBySql(sql)){
            return "OK";
        }else {
            return "Fail";
        }
    }
}
